<?php
/**
 * 数据库PDO操作
 * 注：使用此文件前，需要定义数据库的基本信息
 * $dsn = 'mysql:host=127.0.0.1;port=3333;dbname=testdb', $username, $userpwd;
 */
class PDOX {
	private  $statement  = null;  //PDOStatment

    private  $pdo		 = null;  //单件模式,保存Pdo类唯一实例,数据库的连接资源

    public	 $_sql 	 = '';    //当前SQL语句

    private  $lastInsertId = null;    //最后插入记录的ID

    private  $numRows 	   = 0;    //返回影响记录数

    public function __construct($dsn = 'mysql:host=localhost;port=3306;dbname=test', $username = 'root', $userpwd = '123456'){
        if (!class_exists('PDO')) exit('{"status":"300","msg":"PDO error"}');

		if(empty($dsn)) exit('{"status":"301","msg":"DSN error"}');

		if(is_object($this->pdo)) {
			return $this->pdo;
		}

		//$attrConfig = array('PDO::ATTR_PERSISTENT'=>true, 'PDO::ATTR_EMULATE_PREPARES'=>false,);
		try {
			$this->pdo = new PDO($dsn, $username, $userpwd);
			$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
		}catch(PDOException $e){
			exit('{"status":"302","msg":"connect DB error"}');
		}

		if(!$this->pdo) exit('{"status":"303","msg":"res error"}');

		$this->pdo->exec('SET NAMES utf8');
		//$this->pdo->exec('SET CHARACTER SET utf8');//有些时候会出问题

		return $this->pdo;
	}

	//开始查询
	public function query($sql)
	{
		if(empty($sql)) exit('empty sql!');

		$this->_sql = $sql;
		try{
        	$this->statement = $this->pdo->query($sql);
			if(!$this->statement) exit($this->errorInfo_().'：'.$sql);
        	return $this;
		}catch(PDOException  $e){
			exit($this->errorInfo_());
		}
	}

	public function select($table, $where='')
	{
		if(empty($table)) exit('empty table!');

		$sql = 'select * from '.$table;
		if(!empty($where) && is_array($where)){
			$sql .= ' where '. $this->arrayTostring($where);
            $use_bind = 1;
		}elseif(!empty($where) && !is_array($where)){
			$sql .= ' where '. $where;
		}

		$this->_sql = $sql;
		try{
            if(empty($use_bind)){
        	    $this->statement = $this->pdo->query($sql);
            }else{
                $this->statement = $this->pdo->prepare($sql);
                $data = $this->arrayTostring($where, 'and', 1);
                $this->statement->execute($data);
            }
			if(!$this->statement) exit($this->errorInfo_());
        	return $this;
		}catch(PDOException  $e){
			exit($this->errorInfo_());
		}
	}


	public function prepare($sql)
	{
		if(empty($sql)){
			exit('empty sql!');
        }
		$this->_sql = $sql;
		try{
        	$this->statement = $this->pdo->prepare($sql);
			if(!$this->statement) exit($this->errorInfo_());
        	return $this->statement;
		}catch(PDOException $e){
			exit($this->errorInfo_());
		}
	}

	//获取一条数据，返回一维数组
	public function fetch()
	{
		$t_ = $this->statement->fetch();

		return empty($t_) ? array() : $t_;
	}

	//获取多条数据，返回二维数组
	public function fetchAll()
	{
		return $this->statement->fetchAll();
	}

	//返回一个表字段内容
	public function fetchColumn($intColumn = 0)
	{
		return $this->statement->fetchColumn($intColumn);
	}

	//执行sql语句，主要用于无状态更新
	public function exec($sql)
	{
        try{
			$this->_sql = $sql;
			$this->numRows = $this->pdo->exec($sql);
            return $this->numRows;
        }catch(PDOException $e){
			exit($this->errorInfo_());
        }
    }

	//插入数据后的ID
	public function lastInsertId()
	{
		return $this->pdo->lastInsertId();
	}

	//
	public function execute($param = ""){
        try{
        	if(is_array($param))
                return $this->statement->execute($param);
			else
            	return $this->statement->execute();
         }catch (PDOException $e){
			exit($this->errorInfo_());
        }
    }

	//受影响的行数
	public function rowCount(){
        return $this->numRows;
    }

	/*
 		$table 表名，string
		$arrayData，字段与数据, 一维array
	*/
	public function insert($table, $arrayData){
			if(empty($table) || !is_array($arrayData)){
				exit('insert:data is not array or table is not exists!');
			}

			$a = $cols  = $vals = $data = array();
			foreach($arrayData as $k => $v){
				$cols[] = '`' . $k . '`';
				$vals[] =  ':' . $k . '';
				$data[':' . $k . ''] =  $v;
				$a[] = '\''. addslashes($v) .'\'';
			}

			$sql  = "INSERT INTO {$table} (";
			$sql .= implode(",", $cols) . ") VALUES (";
			$sql .= implode(",", $vals) . ")";

			$this->_sql = "INSERT INTO {$table} (".implode(',', $cols).") VALUES(".implode(',', $a).")";

			try{
				$this->statement = $this->pdo->prepare($sql);
				$this->statement->execute($data);
			}catch(PDOException $e){
				exit($this->errorInfo_());
			}
			$this->numRows = $this->statement->rowCount();
			return  $this->lastInsertId = $this->pdo->lastInsertId();
	}


	public function insertBatch($table, $arrayData){
		if(empty($table) || !is_array($arrayData)){
			exit('insertBatch:data is not array or table is not exists!');
		}

		if(empty($arrayData[0]) || !is_array($arrayData[0]))
            exit('insertBatch need data like data[0][0], data[1][0]!');

		foreach($arrayData[0] as $k => $v){
			$cols[] = '`' . $k . '`';
            $vals[] = ':' . $k . '';
		}
        //make easy SQL to see
		$values = array();
		foreach($arrayData  as $k => $v){
			$a = array_values($v);
			$values[] = '(' . implode(',', array_map(function($value){
				return   "'". addslashes($value) ."'";//外层过滤数据，避免双重过滤，导致结果异常
			}, $a)) . ')';
		}

		$sql  = "INSERT INTO {$table} (" . implode(",", $cols) . ") VALUES  " . implode(",", $values) . "";

		$this->_sql = $sql;

		//$this->numRows = $num = $this->exec($sql);//这种插入数据方式太容易出问题了

        //绑定开始
        $sql  = "INSERT INTO {$table} (";
		$sql .= implode(",", $cols) . ") VALUES(";
		$sql .= implode(",", $vals) . ")";
        $num  = 0;
        $stm  = $this->pdo->prepare($sql);
        $this->pdo->beginTransaction();
        foreach($arrayData  as $k => $v){
            foreach($v as $k2 => $v2){
                $bind[':'.$k2.''] = $v2;
            }
            $stm->execute($bind);
            unset($bind);
        }
        $num = 1;
        $this->pdo->commit();

		return  $num; //$this->lastInsertId = $this->pdo->lastInsertId();
	}


	/*
 		$table表名，string
		$fields要更新的字段，array
		$where 条件，array
	*/
	public function update($table, $fields, $where){
        if(empty($table) || empty($fields) || empty($where) || !is_array($fields) || !is_array($where)){
            exit('update:data is not array!');
        }

		$sql	= 'update ' . $table . ' set  ' . $this->arrayTostring($fields, ','). '  where  '. $this->arrayTostring($where);

		$this->_sql = $sql;
        $data = array_merge($this->arrayTostring($where, '', 1), $this->arrayTostring($fields, '', 1));
		try{
			$this->statement = $this->pdo->prepare($sql);
			$this->statement->execute($data);
			return $this->numRows = $this->statement->rowCount();
		}catch(PDOException $e){
            exit($this->errorInfo_());
		}
    }

	/*
 		$table表名,string
		$where删除数据条件，一维array
	*/
	public function delete($table, $where){
        if(empty($table) || empty($where) || !is_array($where)){
            exit('delete:data is not array or table is not exists!');
        }
		$sql   = 'delete from '.$table.'  where '. $this->arrayTostring($where);

		$this->_sql = $sql;
        $where      = $this->arrayTostring($where, '', 1);
		try{
        	$this->statement = $this->pdo->prepare($sql);
        	$this->statement->execute($where);
        	return $this->numRows = $this->statement->rowCount();
		}catch(PDOException $e){
            exit($this->errorInfo_());
		}
    }

    /*生成绑定类型的字符串，仅支持一维数组*/
	private function arrayTostring($arr, $symbol='and', $isVal=0)
	{
		$str = '';
		if(empty($arr)) return $str;
		$a_ = $v_ = array();
		foreach($arr as $k => $v){
			//$a_[] =  '`'. $k .'`' . '="' . addslashes($v) .'"';
			$a_[] =  '`'. $k .'`' . '=:' . $k .'';

			$v_[':'. $k .''] =   $v ;
		}

		$str = implode(' '.$symbol.' ', $a_);

		return empty($isVal) ? $str : $v_;
	}

	private function errorInfo_(){

		$t  = $this->pdo->errorInfo();

		if(empty($t)) $t = $this->statement->errorInfo();

		if($t) return $t[2];

		return '';
	}

    public function beginTransaction()
    {
        $this->pdo->beginTransaction();
    }
    public function rollBack()
    {
        $this->pdo->rollBack();
    }
    public function commit()
    {
        $this->pdo->commit();
    }
    public function errorInfo()
    {
        return $this->pdo->errorInfo();
    }
}
 ?>
